<?php
/**
 * 数据库备份脚本
 * 用于将远程数据库备份到本地
 */

// 数据库配置
$config = [
    'hostname' => '116.62.146.217',
    'database' => 'oa',
    'username' => 'root',
    'password' => 'JyDmXg0407',
    'hostport' => '3306',
    'charset'  => 'utf8mb4',
];

// 备份文件名
$backupFile = 'oa_backup_' . date('Y-m-d_H-i-s') . '.sql';

try {
    echo "开始备份数据库...\n";
    echo "连接到数据库: {$config['hostname']}:{$config['hostport']}\n";
    
    // 创建数据库连接
    $dsn = "mysql:host={$config['hostname']};port={$config['hostport']};dbname={$config['database']};charset={$config['charset']}";
    $pdo = new PDO($dsn, $config['username'], $config['password'], [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);
    
    // 获取所有表名
    $tables = [];
    $stmt = $pdo->query("SHOW TABLES");
    while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
        $tables[] = $row[0];
    }
    
    echo "找到 " . count($tables) . " 个表\n";
    
    // 开始写入备份文件
    $backupContent = "-- OA数据库备份\n";
    $backupContent .= "-- 创建时间: " . date('Y-m-d H:i:s') . "\n";
    $backupContent .= "-- 表数量: " . count($tables) . "\n\n";
    $backupContent .= "SET FOREIGN_KEY_CHECKS=0;\n\n";
    
    // 备份每个表
    foreach ($tables as $table) {
        echo "正在备份表: $table\n";
        
        // 添加表结构
        $backupContent .= "-- 表结构: $table\n";
        $stmt = $pdo->query("SHOW CREATE TABLE `$table`");
        $row = $stmt->fetch(PDO::FETCH_NUM);
        $backupContent .= "DROP TABLE IF EXISTS `$table`;\n";
        $backupContent .= $row[1] . ";\n\n";
        
        // 备份表数据
        $backupContent .= "-- 表数据: $table\n";
        $stmt = $pdo->query("SELECT * FROM `$table`");
        $rowCount = 0;
        
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            $rowCount++;
            $backupContent .= "INSERT INTO `$table` VALUES (";
            $values = [];
            foreach ($row as $value) {
                if ($value === null) {
                    $values[] = 'NULL';
                } else {
                    $values[] = "'" . addslashes($value) . "'";
                }
            }
            $backupContent .= implode(', ', $values) . ");\n";
        }
        
        $backupContent .= "\n";
        echo "  - 已备份 $rowCount 行数据\n";
    }
    
    $backupContent .= "SET FOREIGN_KEY_CHECKS=1;\n";
    
    // 写入文件
    file_put_contents($backupFile, $backupContent);
    
    $fileSize = filesize($backupFile);
    echo "\n备份完成！\n";
    echo "备份文件: $backupFile\n";
    echo "文件大小: " . formatBytes($fileSize) . "\n";
    
} catch (PDOException $e) {
    echo "数据库连接错误: " . $e->getMessage() . "\n";
} catch (Exception $e) {
    echo "备份过程中发生错误: " . $e->getMessage() . "\n";
}

/**
 * 格式化字节大小
 */
function formatBytes($size, $precision = 2) {
    $units = array('B', 'KB', 'MB', 'GB', 'TB');
    
    for ($i = 0; $size > 1024 && $i < count($units) - 1; $i++) {
        $size /= 1024;
    }
    
    return round($size, $precision) . ' ' . $units[$i];
}